import cx_Oracle
from oracle_class import Oracle_Util
import json


# 查询有问题的库存
# 人工查的话，就是如果库存为负数了，就从当前往之前查，哪些单据录的多余了
# 那就往回找，找到库存不为0，中间的单据就有问题

# 查询管理农场
def find_farm_org() -> json:
    sql = "select * from fr_farm_org t where t.management_flg = 'M' order by t.org_code,t.farm_org"
    o = Oracle_Util()
    rs = o.select(sql)
    # o.disconnect()
    # print('管理农场信息:%s' % str(rs))
    return rs


# 遍历农场查询每个农场的栋批
def find_farm_org_location(org_code: str, farm_org):
    sql = "select * from fr_farm_org t where t.org_code = '" + org_code + "'"
    sql = sql + " and t.parent_farm_org  = '" + farm_org + "'  and nvl(t.cancel_flag,'N') = 'N' and t.active_flg = 'Y'"
    o = Oracle_Util()
    rs = o.select(sql)
    # o.disconnect()
    return rs


# 查询栋批的库存
def find_stock(farm_org, org_code):
    sql = "select b.org_code, b.farm_org, b.product_spec, SUM( DECODE(b.transaction_type, '1', b.qty, '2', " \
          "b.qty * -1) ) AS bal_qty, SUM( DECODE( b.transaction_type, '1', b.female_qty, '2', b.female_qty * -1 ) ) " \
          "AS bal_female_qty, SUM( DECODE( b.transaction_type, '1', b.male_qty, '2', b.male_qty * -1 ) ) AS " \
          "bal_male_qty from fr_ms_trn_breeder b where b.org_code = '" + org_code + "' and b.project_code = 'SW' and " \
                                                                                    "b.farm_org = '" + farm_org \
          + "' AND b.period >= ( SELECT MAX(period) FROM fr_mas_close_period p WHERE " \
            "p.org_code = b.org_code AND p.close_type = 'M' AND farm_org = b.org_code AND operation_close_flag = 'A' ) " \
            "group by b.org_code, b.project_code, b.farm_org, b.ref_org_code, b.ref_project_code, b.ref_plant_code, " \
            "b.ref_farm_org, b.product_spec having SUM( DECODE(b.transaction_type, '1', b.qty, '2', b.qty * -1) ) < 0 "
    o = Oracle_Util()
    rs = o.select(sql)
    return rs


def find_error_document(org_code, farm_org, num: int):
    """递归查询有问题的单据"""
    print('')
